Skip to main content

Create Dataset using Join

  • By using this document user will leran to make dataset by using Join.

What is Join?​

  • A JOIN clause is used to combine rows from two tables, based on a related column between them.

Objectives​

  • Create Dataset using Joins.

Prerequisites​

  • For this example we are using Sales.ds, Insurance Claims.ds & Insurance.ds
danger

  • join Dataset will not be used to Join large Data. It will take more time to execute if we use Join Dataset for large Data.

  • User can use Pipeline functionality, when they want to join large dataset.

  • Click here to know more in detail about Pipeline functionality.

Download join.zip file from link given join.zip

  • After downloading the zip file, add it into the OPNBI. To add it into OPNBI follow this Link

Skill Level Required​

  • Basic knowledge of Joins & SQL.
info

To know more about SQL Joins in detail Click here

  • There are three types of Joins available:-

I. Inner Join
II. Left Outer Join
III. Right Outer Join

I. Inner Join​

Inner Join:- Returns records that have matching values in both tables.

  1. Log in to OPNBI using your respective credentials.

  2. Go to Hamburger Menu Docusaurus Slash Introduction > MasterData > Dataset.

  3. To know more in details about Dataset Section Click here .

  4. User can create dataset using 2 options:-

I. From the Footer Menu

Docusaurus Slash Introduction

II. From the Context Menu

Docusaurus Slash Introduction

  1. Click on Create Dataset option & fill the below details:
  • Name: Join Dataset
  • Datasource: Join

Docusaurus Slash Introduction

info

CLick here to know more about Join Window Terminology.

  1. Fill the details as shown in the image below:-
  • Dataset (at the left side): Insurance Claims.ds
  • Dataset Columns: Select all columns available
  • Select Join: Inner Join
  • Dataset (at the right side): Insurance.ds
  • Dataset Columns: Select all columns available

Docusaurus Slash Introduction

  1. User can select columns as many they want to see in the output.

  2. Click on the key button as shown in the image above.

    Docusaurus Slash Introduction

  3. Select Country From both Dataset Keys.

  4. Click on (+) icon Docusaurus Slash Introduction below Key Dataset box.

  5. Select Country||Country from Key Dataset Drop-Down.

    Docusaurus Slash Introduction

  6. Click on Output Columns Tab next to General Tab.

    Docusaurus Slash Introduction

  7. Here user will notice the columns name Insurance Claims:Country & Insurance:Country. Same Country column for Insurance Claims & Insurance dataset.

  8. Click on Preview button.

    Docusaurus Slash Introduction

  9. Click on submit button & your dataset will be created.

II. Left Outer Join​

Left Outer Join:- Returns all records from the left table, and the matched records from the right table.

  1. Follow the same steps to 1 to 5 & countiue to follow below steps in order to make dataset with Left Outer Join.

  2. Fill the below details in Create Daataset window:-

  • Name: Left Join Dataset
  • Datasource: Join

Docusaurus Slash Introduction

  1. Fill the below details in Create Dataset window:-
  • Dataset (at the left side): Sales.ds
  • Dataset Columns: Select country, countrycode, orderDate, productCode, quantityOrdered & priceEach
  • Select Join: Left Outer Join
  • Dataset (at the right side): Insurance.ds
  • Dataset Columns: Select SrNo, Policy_Date, CustomerIDMonth, Year & Country

Docusaurus Slash Introduction

  1. Click on the key button as shown in the image above.

    Docusaurus Slash Introduction

info

In order to join 2 dataset we need to select one column from both dataset having matching data.

  1. Select Country From both Dataset Keys.

  2. Click on (+) icon Docusaurus Slash Introduction below Key Dataset box.

  3. Select Country||Country from Key Dataset Drop-Down.

    Docusaurus Slash Introduction

  4. Click on Output Columns Tab next to General Tab.

    Docusaurus Slash Introduction

  5. Here user will notice the columns name Sales:Country & Insurance:Country. Same Country column for Sales & Insurance dataset.

  6. Click on Preview button.

    Docusaurus Slash Introduction

  7. Scroll the Vertical & Horizontal bar to see the dataset preview.

  8. Compare Sales:country & Insurance:Country to see the difference in dataset.

  9. This dataset includes the all the data of Sales.ds table & matched data of Insurance.ds table.

  10. Click on submit button & your dataset will be created.

III. Right Outer Join​

Right Outer Join:- Returns all records from the right table, and the matched records from the left table.

  1. Follow the same steps to 1 to 5 & countiue to follow below steps in order to make dataset with Right Outer Join.

  2. Fill the below details in Create Dataset window:-

  • Name: Right Outer Join
  • Datasource: Join

Docusaurus Slash Introduction

  1. Fill the below details in Create Dataset window:-
  • Dataset (at the left side): Insurance.ds
  • Dataset Columns: Select SrNo, Policy_Date, CustomerIDMonth, Year & Country
  • Select Join: Right Outer Join
  • Dataset (at the right side): Sales.ds
  • Dataset Columns: Select country, countrycode, orderDate, productCode, quantityOrdered & priceEach

Docusaurus Slash Introduction

  1. Click on the key button as shown in the image above.

    Docusaurus Slash Introduction

  2. Select Country From both Dataset Keys.

  3. Click on (+) icon Docusaurus Slash Introduction below Key Dataset box.

  4. Select Country||Country from Key Dataset Drop-Down.

    Docusaurus Slash Introduction

  5. Click on Output Columns Tab next to General Tab.

    Docusaurus Slash Introduction

  6. Here user will notice the columns name Insurance:Country & Sales:Country. Same Country column for Insurance & Sales dataset.

  7. Click on Preview button.

    Docusaurus Slash Introduction

  8. Scroll the Vertical & Horizontal bar to see the dataset preview.

  9. This dataset include the all data of Sales.ds table & matched data from the Insurance.ds table.